﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using NTQOnlineExam.Services.Infrastructure;
using NTQOnlineExam.Services.Models;
using NTQOnlineExam.Services.SearchCriteria;

namespace NTQOnlineExam.Services.ReportService.Section
{
  class GetSectionDTOBySearchCriteriaDbCommand : ICommand<SearchResult<SectionDTO>>
  {
    private readonly int _pageSize;
    private readonly int _pageIndex;
    private static SectionSearchCriteria _criteria;

    private const string _sql = @"
              WITH PAGING AS (
    SELECT s.SectionId, s.SectionName, s.Description, t.TermId, t.TermName, row_number() over (order by {0}) as rownumber
    FROM dbo.Section s INNER JOIN dbo.Term t ON s.TermId = t.TermId
{1})
        ";

    public GetSectionDTOBySearchCriteriaDbCommand(SectionSearchCriteria criteria, int pageSize, int pageIndex)
    {
      _pageSize = pageSize;
      _pageIndex = pageIndex;
      _criteria = criteria;
    }

    public SearchResult<SectionDTO> Execute()
    {
      var connectionString = ConfigurationManager.AppSettings["ConnectionString"];
      using (SqlConnection connection = new SqlConnection(connectionString))
      {
        IList<SectionDTO> items = new List<SectionDTO>();
        int totalCount = 0;
        SqlCommand cmd = new SqlCommand(BuildQuery(), connection);
        cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = _pageIndex;
        cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = _pageSize;
        connection.Open();
        SqlDataReader reader = cmd.ExecuteReader();
        try
        {
          if (!reader.Read()) return null;
          totalCount = reader.GetInt32(0);
          reader.NextResult();

          while (reader.Read())
          {
            items.Add(GetDto(reader));
          }
        }
        finally
        {
          reader.Close();
        }
        return new SearchResult<SectionDTO>(items, totalCount);
      }
    }
    private SectionDTO GetDto(SqlDataReader reader)
    {
      return new SectionDTO
    {
      SectionId = (int)reader["SectionId"],
      SectionName = reader["SectionName"].ToString(),
      Description = reader["Description"] != DBNull.Value ? reader["Description"].ToString() : string.Empty,
      TermId = (int)reader["TermId"],

      TermName = reader["TermName"] != DBNull.Value ? reader["TermName"].ToString() : string.Empty
    };
    }

    private string BuildQuery()
    {
      var query = new StringBuilder();
      var sqlQuery = string.Format(_sql, BuildOrderClause(), BuildWhereClause());
      query.AppendFormat("{0} {1};{0} {2}", sqlQuery, @"SELECT COUNT(1) FROM paging", @" SELECT * FROM paging WHERE (@PageSize=0 or rownumber between @PageSize*@PageIndex+1 and @PageSize*(@PageIndex+1)) ORDER BY rownumber");
      return query.ToString();
    }

    private string BuildWhereClause()
    {
      string whereClause = string.Empty;
      if (!string.IsNullOrEmpty(_criteria.Keyword))
        whereClause = string.Format("s.SectionName LIKE '%{0}%'", _criteria.Keyword.Trim());

      if (!string.IsNullOrEmpty(_criteria.TermKeyword))
        whereClause += string.Format("AND t.TermName LIKE '%{0}%'", _criteria.TermKeyword.Trim());

      if (whereClause.Trim().StartsWith("AND", StringComparison.InvariantCultureIgnoreCase))
        whereClause = whereClause.Trim().Remove(0, 3);

      if (!String.IsNullOrEmpty(whereClause))
        return " WHERE " + whereClause;
      return string.Empty;
    }

    private static string BuildOrderClause()
    {
      StringBuilder orderClause = new StringBuilder();
      foreach (SearchSortItem sortItem in _criteria.SortItems)
      {
        if (sortItem.SortExpression.Equals("TermName", StringComparison.OrdinalIgnoreCase))
        {
          orderClause.AppendFormat("t.TermName {0}", sortItem.SortOrder == SortOrder.Ascending ? "ASC" : "DESC");
        }
        else
          orderClause.AppendFormat("s.{0} {1}", sortItem.SortExpression, sortItem.SortOrder == SortOrder.Ascending ? "ASC" : "DESC");
        orderClause.Append(',');
      }

      if (orderClause.Length <= 0)
      {
        orderClause.Append("SectionName ASC");
      }
      else
      {
        orderClause.Remove(orderClause.Length - 1, 1);
      }
      return orderClause.ToString();
    }
  }
}
